﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.ProviderBase;
using System.Data.SqlClient;
using System.Data.Common;

namespace Code
{
    public class DataProvider
    {
        private DbConnection _connection;
        private DbProviderFactory _factory;
        private DbCommand _command;
        private string _error;

        // Public Properties
        public string ConnectionString
        {
            get
            {
                return _connection.ConnectionString;
            }
            set
            {
                _connection.ConnectionString = value;
            }
        }

        public DbConnection Connection
        {
            get
            {
                return _connection;
            }
        }

        public DbCommand Command
        {
            get
            {
                return _command;
            }
        }

        public DbProviderFactory Factory
        {
            get
            {
                return _factory;
            }
        }
        public string LastError
        {
            get
            {
                return _error;
            }
            set
            {
                _error = value;
            }
        }

        public DbCommand CreateCommand()
        {
            return _factory.CreateCommand();
        }

        public DbParameter CreateParameter()
        {
            return _factory.CreateParameter();
        }

        // Contructor
        private DataProvider()
        {
            string providerInvariantName = ConfigurationManager.AppSettings["ProviderInvariantName"].ToString();
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

            _factory = DbProviderFactories.GetFactory(providerInvariantName);

            _connection = _factory.CreateConnection();
            _connection.ConnectionString = connectionString;

            _command = _factory.CreateCommand();
            _command.Connection = this.Connection;

            _error = "";
        }

        // Static Property
        private static DataProvider _dataProvider;
        public static DataProvider GlobalDataProvider
        {
            get
            {
                if (_dataProvider == null)
                    _dataProvider = new DataProvider();
                return _dataProvider;
            }
        }

        // check if this query need transaction
        public bool checkNeedTransaction(string strSQL)
        {
            string temp = strSQL.ToLower();
            if (temp.Contains("insert")
                || temp.Contains("delete")
                || temp.Contains("update")
                )
            {
                return true;
            }
            return false;
        }

        // Execute Query
        public DbDataReader ExecuteQuery(string strSQL)
        {
            this.Command.CommandText = strSQL;
            this.Command.CommandType = CommandType.Text;

            return ExecuteQuery(this.Command, checkNeedTransaction(strSQL));
        }

        // CORE
        public DbDataReader ExecuteQuery(DbCommand command, bool bNeedTransaction)
        {
            DbDataReader dataReader = null;
            DbTransaction transaction = null;
            try
            {
                Normalize(command);
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                if (bNeedTransaction)
                {
                    transaction = this.Connection.BeginTransaction();
                    command.Transaction = transaction;
                    dataReader = command.ExecuteReader();
                    transaction.Commit();
                }
                else
                    dataReader = command.ExecuteReader();
                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
                if (bNeedTransaction)
                {
                    // rollback
                    if (transaction != null)
                        transaction.Rollback();
                }
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }
            return dataReader;
        }

        // CORE
        public DbDataReader ExecuteQuery(DbCommand command)
        {
            DbDataReader dataReader = null;
            try
            {
                Normalize(command);
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();

                dataReader = command.ExecuteReader();
                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }
            return dataReader;
        }

        // Execute Scalar
        public object ExecuteScalar(string strSQL)
        {
            this.Command.CommandText = strSQL;
            this.Command.CommandType = CommandType.Text;

            return ExecuteScalar(this.Command, checkNeedTransaction(strSQL));
        }


        // CORE
        public object ExecuteScalar(DbCommand command, bool bNeedTransaction)
        {
            object scalar = null;
            try
            {
                Normalize(command);
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                scalar = command.ExecuteScalar();
                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }

            return scalar;
        }

        public object ExecuteScalar(DbCommand command)
        {
            object scalar = null;
            try
            {
                Normalize(command);
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                scalar = command.ExecuteScalar();
                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }

            return scalar;
        }

        // Execute Non Query
        public int ExecuteNonQuery(string strSQL)
        {
            this.Command.CommandText = strSQL;
            this.Command.CommandType = CommandType.Text;

            return ExecuteNonQuery(this.Command, checkNeedTransaction(strSQL));
        }

        public int ExecuteNonQuery(string strSQL, bool needTransaction)
        {
            this.Command.CommandText = strSQL;
            this.Command.CommandType = CommandType.Text;

            return ExecuteNonQuery(this.Command, needTransaction);
        }

        // CORE
        public int ExecuteNonQuery(DbCommand command, bool bNeedTransaction)
        {
            DbTransaction transaction = null;
            int i = -1;
            try
            {
                Normalize(command);
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                if (bNeedTransaction)
                {
                    transaction = this.Connection.BeginTransaction();
                    command.Transaction = transaction;
                    i = command.ExecuteNonQuery();
                    transaction.Commit();
                }
                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
                if (bNeedTransaction && transaction != null)
                {
                    transaction.Rollback();
                }
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }

            return i;
        }

        public int ExecuteNonQuery(DbCommand command)
        {
            int i = -1;
            try
            {
                Normalize(command);
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();

                i = command.ExecuteNonQuery();

                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }

            return i;
        }

        // Execute Query DataSet
        public DataSet ExecuteQueryDataSet(string strSQL)
        {
            this.Command.CommandText = strSQL;
            this.Command.CommandType = CommandType.Text;

            return ExecuteQueryDataSet(this.Command, checkNeedTransaction(strSQL));
        }

        // CORE
        public DataSet ExecuteQueryDataSet(DbCommand command, bool bNeedTransaction)
        {
            DataSet ds = new DataSet();
            try
            {
                DbDataAdapter dataAdapter = this.Factory.CreateDataAdapter();
                Normalize(command);
                dataAdapter.SelectCommand = command;
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                dataAdapter.Fill(ds);
                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }

            return ds;
        }

        public DataSet ExecuteQueryDataSet(DbCommand command)
        {
            DataSet ds = new DataSet();
            try
            {
                DbDataAdapter dataAdapter = this.Factory.CreateDataAdapter();
                Normalize(command);
                dataAdapter.SelectCommand = command;
                command.Connection = this.Connection;
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                dataAdapter.Fill(ds);
                this.LastError = "";
            }
            catch (Exception ex)
            {
                this.LastError = ex.Message;
            }
            finally
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
            }

            return ds;
        }

        // Execute Query DataTable
        public DataTable ExecuteQueryDataTable(string strSQL)
        {
            DataTable table = null;
            DataSet ds = ExecuteQueryDataSet(strSQL);
            if (ds != null && ds.Tables.Count > 0)
                table = ds.Tables[0];
            return table;
        }

        // CORE

        public DataTable ExecuteQueryDataTable(DbCommand command)
        {
            DataTable table = null;
            Normalize(command);
            DataSet ds = ExecuteQueryDataSet(command);
            if (ds != null && ds.Tables.Count > 0)
                table = ds.Tables[0];
            return table;
        }


        private void Normalize(DbCommand command)
        {
            string providerInvariantName = ConfigurationManager.AppSettings["ProviderInvariantName"].ToString();
            if (providerInvariantName == "System.Data.OleDb" && command.Parameters.Count > 0)
            {
                string strSQL = command.CommandText.ToLower();
                foreach (DbParameter param in command.Parameters)
                {
                    string oldName = "@" + param.ParameterName.ToLower();
                    int startIndex = strSQL.IndexOf(oldName);
                    if (startIndex >= 0)
                    {
                        strSQL = strSQL.Remove(startIndex, oldName.Length);
                        strSQL = strSQL.Insert(startIndex, "?");
                    }
                }
                command.CommandText = strSQL;
            }
        }
    }
}
